﻿CREATE VIEW [dbo].[BAM_837I_835]
AS 
	select BTSMessageID, BTSInterchangeID, MessageID, 'TransactionStatus' = 
     CASE
          WHEN (select count(*) from dbo.BAM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) = 1 THEN 'Completed'
		  WHEN (select count(*) from dbo.BAM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) > 1 THEN 'Duplicate BHT03 ID'
          ELSE 'Incomplete'
     END, (Select top 1 BTSMessageID from dbo.BAM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) as 'BTSMessageID_835', (Select top 1 BTSInterchangeID from dbo.BAM_835 b835 where b837I.Claim_CLM01_PatientAccountNumber = b835.ClaimPayment_CLP01_PatientAccountNumber) as 'BTSInterchangeID_835'

from BAM_837I b837I
;
--Drop VIEW [dbo].[BAM_837I_835]